/*
 **************************************************************************
 * @author Armen
 * Made:Dec 9, 2009
 * 
 *  
 ************************************************************************** 
 */
package onlinecalsys.server.database;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class MySQLDatabaseLayer extends MySQLDatabaseSetup implements
		DatabaseLayer {

	/** The statement. */
	private Statement statement;

	/** The result set. */
	private ResultSet resultSet;

	private static MySQLDatabaseLayer dblayer;

	private MySQLDatabaseLayer() {

	}

	public static MySQLDatabaseLayer getInstance() {
		if (dblayer == null) {
			dblayer = new MySQLDatabaseLayer();
		}

		return dblayer;
	}

	@Override
	public synchronized boolean storeAppointment(String username, String date,
			int time, String Appointment) {

		try {
			System.out.println(date);
			statement = connect();
			String query = "insert into appointments (Time, DateApp, Appointment) values ('"
					+ time + "', '" + date + "', '" + Appointment + "');";
			System.out.println(date);

			statement.executeUpdate(query, Statement.RETURN_GENERATED_KEYS);
			resultSet = statement.getGeneratedKeys();
			int appID = -1;
			if (resultSet.next()) {
				appID = resultSet.getInt(1);
			}

			resultSet = statement
					.executeQuery("select UserId from users where Username = '"
							+ username + "';");
			String userId = "";

			if (resultSet.next()) {
				userId = resultSet.getString(1);
			}

			System.out.println("INSERT INTO appandusers VALUES (" + appID
					+ ", " + userId + ");");
			statement.executeUpdate("INSERT INTO appandusers VALUES (" + appID
					+ ", " + userId + ");");

			return true;

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

	}

	/**
	 * This method returns a list of appointments for a specific user and a
	 * specific date
	 * 
	 * @param date
	 * @param username
	 * @return
	 * @throws InterruptedException
	 */
	public synchronized ArrayList<ArrayList<String>> getAppointments(
			String date, String username) throws InterruptedException {
		try {
			statement = connect();

			String query = "select UserId from users where Username = '"
					+ username + "';";
			resultSet = statement.executeQuery(query);

			int userId = -1;
			while (resultSet.next()) {
				userId = resultSet.getInt(1);
			}

			query = "select AppointmentId from appandusers where UserId ="
					+ userId + ";";

			resultSet = statement.executeQuery(query);

			ArrayList<Integer> appointmentsIDs = new ArrayList<Integer>();
			while (resultSet.next()) {
				appointmentsIDs.add(resultSet.getInt(1));
			}

			ArrayList<ArrayList<String>> applist = new ArrayList<ArrayList<String>>();
			for (int i = 0; i < appointmentsIDs.size(); i++) {
				resultSet = statement
						.executeQuery("select * from appointments where AppointmentId ="
								+ appointmentsIDs.get(i)
								+ " AND DateApp = '"
								+ date + "';");
				ArrayList<String> app = new ArrayList<String>();
				while (resultSet.next()) {
					app.add(resultSet.getString(1));
					app.add(resultSet.getString(2));
					app.add(resultSet.getString(3));
					app.add(resultSet.getString(4));
					applist.add(app);
				}

			}
			return applist;
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				return null;
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				return null;
			}
		}

	}

	@Override
	public synchronized boolean deleteAppointment(int appointmentId,
			String username) {
		try {
			statement = connect();
			String query = "select UserId from users where Username = '"
					+ username + "';";

			resultSet = statement.executeQuery(query);

			int userId = -1;
			while (resultSet.next()) {
				userId = resultSet.getInt(1);
			}

			query = "select * from appandusers where UserId =" + userId
					+ " AND AppointmentId  = " + appointmentId + ";";
			resultSet = statement.executeQuery(query);

			if (!resultSet.next()) {
				return false;
			} else {
				query = "delete from appointments where AppointmentId ="
						+ appointmentId + ";";

				statement.executeUpdate(query);

				return true;

			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				return false;
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
				return false;
			}
		}

	}

	@Override
	public boolean usernameChecking(String username, String password) {
		try {
			statement = connect();
			String query = "select * from users where Username = '" + username
					+ "' AND Password = '" + password + "';";
			resultSet = statement.executeQuery(query);

			if (resultSet.next()) {
				return true;
			} else {
				return false;
			}

		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
	}

	@Override
	public boolean shareAppointment(String username, int id,
			String shareWithUsername) {
		try {
			statement = connect();
			// First I get the ID of the username.
			String query = " select UserId from users where Username = '"
					+ username + "';";

			resultSet = statement.executeQuery(query);

			int userId = -1;
			while (resultSet.next()) {
				userId = resultSet.getInt(1);
			}

			// Here i verify that the username and the appointment that he/she
			// wants to share is an appointment made by the same user
			query = "select * from appandusers where UserId = " + userId
					+ " AND AppointmentId = " + id + ";";

			resultSet = statement.executeQuery(query);
			// I just return false if the username and ID of appointment doesnt
			// match
			if (!resultSet.next()) {
				return false;
			}
			// Here i get the userid of the username that the appointment is
			// being shared with
			query = "select UserId from users where Username = '"
					+ shareWithUsername + "';";

			resultSet = statement.executeQuery(query);
			int sharedID = -1;
			while (resultSet.next()) {
				sharedID = resultSet.getInt(1);
			}

			// Here I check to see if there is and Username for the user that
			// the appointment is being shared to
			if (sharedID == -1) {
				return false;
			}

			// now I check to see if this sharing already exist.
			query = "select * from sharedapps where AppointmentId = " + id
					+ " AND UserId = " + userId + "  AND SharedToUserId = "
					+ sharedID + ";";

			resultSet = statement.executeQuery(query);
			if (resultSet.next()) {
				return false;
			}

			// so now I insert the 3 ids i have into the sharedapps table in the
			// database

			query = "insert into sharedapps values (" + id + ", " + userId
					+ ", " + sharedID + ");";

			statement.executeUpdate(query);

			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

	}

	@Override
	public ArrayList<ArrayList<String>> getSharedAppointments(String username) {
		try {
			ArrayList<ArrayList<String>> list = new ArrayList<ArrayList<String>>();
			statement = connect();
			// First i get the userid of the username from the parameter
			String query = " select UserId from users where Username = '"
					+ username + "';";

			resultSet = statement.executeQuery(query);

			int userId = -1;
			while (resultSet.next()) {
				userId = resultSet.getInt(1);
			}

			// now i get all of the appointments that are shared with him/her
			query = "select AppointmentId, UserId from sharedapps where SharedToUserId = " + userId
					+ ";";

			resultSet = statement.executeQuery(query);
			
			ArrayList<Integer> appsIds = new ArrayList<Integer>();
			ArrayList<Integer> userIds = new ArrayList<Integer>();
			while (resultSet.next()) {
				appsIds.add(resultSet.getInt(1));
				userIds.add(resultSet.getInt(2));

			}
			
			for(int i = 0; i < appsIds.size(); i++) {
				query = "select * from appointments where AppointmentId = " + appsIds.get(i) + ";";
				resultSet = statement.executeQuery(query);
								
				ArrayList<String> apps = new ArrayList<String>();
				while(resultSet.next()) {
					apps.add(resultSet.getString(1));
					apps.add(resultSet.getString(2));
					apps.add(resultSet.getString(3));
					apps.add(resultSet.getString(4));
				}
				
				query = "select Username from users where UserId = " + userIds.get(i) + ";";
				
				resultSet = statement.executeQuery(query);
				
				while(resultSet.next()) {
					apps.add(resultSet.getString(1));
				}
				list.add(apps);
			}
			return list;

		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
				
			} catch (SQLException ex) {
				ex.printStackTrace();
				return null;
			}
		}
		

	}

	@Override
	public boolean saveNote(String note, String username) {
	
		try {
			statement = connect();
			String query = "UPDATE `onlinecalsys`.`users` SET `Note` = '" + note + "' WHERE `users`.`Username` ='"+username+"';";
		

			statement.executeUpdate(query);
			
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}

	}

	@Override
	public String getNote(String username) {
	
		try {
			statement = connect();
			String query = "select Note from users where `Username` ='"+username+"';";
		

			resultSet = statement.executeQuery(query);
			String noteString = null;
			while(resultSet.next()) {
				noteString = resultSet.getString(1);
			}
			
			return noteString;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return null;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		
	
		
		
		
		
	}

	@Override
	public boolean createUser(String username, String password) {
		try {
			statement = connect();
			String query = "insert into users (Username, Password, Note) values ('" + username + "', '" + password + "', '');";
		

			statement.executeUpdate(query);
			
			
			return true;
			
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			try {
				if (resultSet != null)
					resultSet.close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
			try {
				close();
			} catch (SQLException ex) {
				ex.printStackTrace();
			}
		}
		
	
		
	}

		
		
		
	}



;